from playwright.async_api import async_playwright, Page,Playwright
from tool import openSunBrowser,tms,get_kol,get_all_active_sunbrowser
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from myexcel import getDictDatasByEecel2
from api import js_xp_del_pro
from tool import *
from papi import *
import asyncio,json,hashlib
import requests,math,uuid,openpyxl,dirtyjson
import concurrent.futures as thddd

ADSPower_API_KEY = '你的API_KEY'
ADSPower_API_URL = 'http://127.0.0.1:50325'  # 根据你的实际设置修改

async def connect_browser_pw2():
    params={
        'profile_id':'kypn401',
        'last_opened_tabs':'1',
        'proxy_detection':'0'
    }


    res = requests.post(f'{ADSPower_API_URL}/api/v2/browser-profile/start', json=params, timeout=180)
    res_data = res.json()

    if res.status_code != 200 or res_data.get('code') != 0:
        raise Exception(f"启动 ADS 浏览器失败: {res_data}")

    ws_url = res_data['data']['ws']['puppeteer']
    playwright = await async_playwright().start()
    browser = await playwright.chromium.connect_over_cdp(ws_url)

    return playwright, browser

async def ttt(page: Page):
    while True:
        title = await page.title()
        print("网页标题是：", title)
        await asyncio.sleep(3)

async def main():
    playwright, browser = await connect_browser_pw2()
    ctx = browser.contexts[0]
    binfo= {
        'id': 137, 'BrowserID': 'kypn401', 'DpName': '开发测试', 'GroupName': 'POD - 胡伟国 - 99000 - 亚细安', 'Platform': 'seller.shopee.co.th', 
        'Country': '印尼', 'PingTai': 'shopee', 'UserName': 'c84rdsfrn8', 'Password': 'Tk112211@', 'SJBM': 'hwg985', 'YYBM': 'id8897', 
        'facName': '亚细安', 'PODPrice': 99000, 'ProxyIP': 'socks5://14a1a6205d8a4:645b3832f7@188.64.179.182:12324', 
        'isHomeInfo': 1, 'isBB': 0, 'isbigdata': 0, 'isCreAd': 0, 'isDelete': 0, 'isAddCart': 0, 'isBBAdID': 0, 'isBBAdInfo': 0, 'isAddKw': 0, 
        'isEditKw': 0, 'isCjLL': 1, 'isDelKw': 0, 'isDelNoClick': 0, 'isDraft': 0, 'isCJMyList': 1, 'isCJWG': 1, 'isAddCost': 0, 
        'isPODPubilsh': 0, 'isBanned': 0, 'is_pause_ad': 0, 'is_task_baned': 0, 'pod_dc_type': 1, 'is_close': 0,'is_KJ':0,'shopid':None,'host':'seller.shopee.co.th'
        }
    page = await ctx.new_page()
    await page.goto('https://seller.shopee.co.th/portal/marketing/pas/index',timeout=60000)

    #await plw_wait_xpath(page,'//span[@class="title-text" and text()="Product Selection "]')
    await asyncio.sleep(5)
    print('开始采集')
    res=await plw_xp_pause_ad(page,binfo,[110776309])
    print(res)
    await asyncio.sleep(1000)
    await browser.close()
    await playwright.stop()

async def main2():
    binfo= {
        'id': 137, 'BrowserID': 'kypn401', 'DpName': '胡伟国虾皮4号店铺', 'GroupName': 'POD - 胡伟国 - 99000 - 亚细安', 'Platform': 'https://seller.shopee.co.id/', 
        'Country': '印尼', 'PingTai': 'shopee', 'UserName': 'c84rdsfrn8', 'Password': 'Tk112211@', 'SJBM': 'hwg985', 'YYBM': '', 
        'facName': '亚细安', 'PODPrice': 99000, 'ProxyIP': 'socks5://14a1a6205d8a4:645b3832f7@188.64.179.182:12324', 
        'isHomeInfo': 1, 'isBB': 0, 'isbigdata': 0, 'isCreAd': 0, 'isDelete': 0, 'isAddCart': 0, 'isBBAdID': 0, 'isBBAdInfo': 0, 'isAddKw': 0, 
        'isEditKw': 0, 'isCjLL': 1, 'isDelKw': 0, 'isDelNoClick': 0, 'isDraft': 0, 'isCJMyList': 1, 'isCJWG': 1, 'isAddCost': 0, 
        'isPODPubilsh': 0, 'isBanned': 0, 'is_pause_ad': 0, 'is_task_baned': 0, 'pod_dc_type': 1, 'is_close': 0,'is_KJ':0
        }
    plw,browser=await connect_browser_pw(binfo,7)
    ctx = browser.contexts[0]
    page = await ctx.new_page()
    await page.goto('https://www.baidu.com')
    await asyncio.sleep(10)

    await page.close()
    await plw.stop()
    await asyncio.sleep(100)
    await close_SunBrowser('kypn401',7)

    # with open('虾皮4店帮删除.txt','r',encoding='utf-8') as f:

    #     cot=f.read()
    
    # itemid_list=[int(itemid) for itemid in cot.split() if itemid]

    # for j in range(math.ceil(len(itemid_list)/12)):
    #     cur_itemid_list=itemid_list[j*12:(j+1)*12]
    #     res=await js_xp_del_pro(page,binfo,cur_itemid_list)
    #     print(f'第{j+1} 页,删除结果 => {res}')

def kchrome():
    import os
    import psutil
    pids = psutil.pids()
    for pid in pids:
        try:
            p = psutil.Process(pid)
            # print('pid-%s,pname-%s' % (pid, p.name()))
            if p.name() == 'nginx.exe':
                #print(f'关闭{pid} => {p.name()}')
                cmd = 'taskkill /F /IM nginx.exe'
                os.system(cmd)

        except Exception as e:
            pass

def md5_encrypt(text: str) -> str:
    """
    功能：对字符串进行MD5加密
    参数：
        text: 需要加密的字符串
    返回：
        32位小写MD5字符串
    """
    md5 = hashlib.md5()
    md5.update(text.encode('utf-8'))
    return md5.hexdigest()


async def ppy():

    plw,browser=await connect_browser_pw2()
    ctx = browser.contexts[0]
    page = await ctx.new_page()
    await page.goto('https://www.baidu.com')
    #await asyncio.sleep(100000)
    #await asyncio.sleep(3)
    raise ValueError
    await page.close()

    await plw.stop()
    close_SunBrowser('kypn401',1)

def upppp(kj,pid,pname,state):
    if state==1:
        aff=tms.ExecNoQuery('update s1688pro set pname=?,is_cl_pname=1 where id=?',(pname,pid))
    else:
        aff=tms.ExecNoQuery('update s1688pro set pname=?,is_cl_pname=? where id=?',(pname,state,pid))

    return (kj,pid,aff)

def pname_gl():
    rs_ppp=tms.ExecQuery('select count(*) from s1688pro where is_cl_pname=0')
    all_pcount=rs_ppp[0][0]
    print(f'共 {all_pcount} 个产品待过滤标题')
    lc=1
    while True:
        rs=tms.ExecQuery('select top 1000 id,pname from s1688pro where is_cl_pname=0')
        if len(rs)==0:
            print('全部过滤完毕')
            break
        print(f'本轮提取 {len(rs)} 个产品标题...')
        need_gl=0
        gl_ignore=0
        has_gl=0
        with thddd.ThreadPoolExecutor(max_workers=20) as t:
            tasks=[]

            for kj,r in enumerate(rs,start=1):
                pid,pname=r
                pname_new=clean_html(pname)
                if pname_new!=pname:
                    tasks.append(t.submit(upppp,kj,pid,pname_new,1))
                    need_gl+=1
                else:
                    tasks.append(t.submit(upppp,kj,pid,pname_new,2))
                    gl_ignore+=1

            for t0 in thddd.as_completed(tasks):
                res_kj,res_pid,res_aff=t0.result()
                if res_aff==1:
                    #print(f'第 {res_kj}个,pid:{res_pid},标题成功过滤')
                    has_gl+=1
        
        all_pcount-=len(rs)
        print(f'第 {lc} 轮需过滤:{need_gl},可忽略:{gl_ignore},实际过滤:{has_gl},剩余:{all_pcount},{math.ceil(all_pcount/1000)}轮')
        lc+=1


def upppp2(kj,pitem):
    pid,pname,skw=pitem
    skw_list=remove_all_punct(skw).split()
    is_bh=is_pname_contain_klist(pname,skw_list)
    aff=tms.ExecNoQuery('update s1688pro set is_contain_kw=? where id=?',(is_bh,pid))


    return (kj,is_bh,aff)

def main3():
    rs_ppp=tms.ExecQuery('select count(*) from s1688pro where is_contain_kw is null and keword_gender=3')
    all_pcount=rs_ppp[0][0]
    print(f'共 {all_pcount} 个产品待过滤标题')
    lc=1
    while True:
        rs=tms.ExecQuery('select top 1000 id,pname,from_keywords from s1688pro where is_contain_kw is null and keword_gender=3')
        if len(rs)==0:
            print('全部过滤完毕')
            break
        print(f'本轮提取 {len(rs)} 个产品标题...')
        contain_count=0
        not_contain_count=0
        with thddd.ThreadPoolExecutor(max_workers=20) as t:
            tasks=[]

            for kj,r in enumerate(rs,start=1):
                tasks.append(t.submit(upppp2,kj,r))

            for t0 in thddd.as_completed(tasks):
                res_kj,res_state,res_aff=t0.result()
                if res_aff==1:
                    #print(f'第 {res_kj}个,pid:{res_pid},标题成功过滤')
                    if res_state:
                        contain_count+=1
                    else:
                        not_contain_count+=1

        
        all_pcount-=len(rs)
        print(f'第 {lc} 轮需过滤:{len(rs)},包含关键词:{contain_count},不包含关键词:{not_contain_count},剩余:{all_pcount},{math.ceil(all_pcount/1000)}轮')
        lc+=1
        
def task_get_cpc_and_risk():

    sql_str1=f'''select top 1 itemid,title from ShopeeAdsReport 
                    where dtype='last_month' 
                    and yybm='mylxh999' 
                    and click > 4 
                    and ctr > 0.03 
                    and cost/click < 0.11
                    and keyword_status is null
            '''
    rs_tits=tms.ExecQuery(sql_str1)
    session=requests.session()
    session.headers=DS_Headers
    print(f'本次共取出 {len(rs_tits)} 个标题进行提取标题关键词和侵权词')
    
    for rj,r in enumerate(rs_tits,start=1):
        itemid,title=r
        print(f'标题:{title}')
        print(f'第{rj}个itemid:{itemid},开始采集提取标题关键词和侵权词...')    
        question1=f'{title}\n从这个产品名称中提取1个中文品名词和1个中文核心属性词。只回答结果即可,回答格式:品名词 属性词'
        question2=f'{title}\n从这个标题里面翻译并提取最可能侵权的一个中文词。只回答侵权词结果,如果没有侵权词就回答无'
        res_str1=ask_deepseek(session,question1)
        print(res_str1)
        res_str2=ask_deepseek(session,question2)
        print(res_str2)
        if res_str1 and res_str2:
            res_str1=res_str1.strip('（）《》<>[]()【】 ')
            res_str2=res_str2.strip('（）《》<>[]()【】 ')
            aff_change_state=tms.ExecNoQuery('update ShopeeAdsReport set keyword_cpc=?,keyword_risk=?,keyword_status=? where itemid=? and dtype=?',(res_str1,res_str2,'已提取',itemid,'last_month'))
            print(f'第{rj}个itemid:{itemid},提取关键词结果:《{res_str1}》,提取侵权词结果:《{res_str2}》,更改状态:{aff_change_state}')

        else:
            print(f'第{rj}个itemid:{itemid},提取标题关键词和侵权词失败')
        
        print(f'本次剩余采集队列:{len(rs_tits)-rj}/{len(rs_tits)}')

def task_extract_1688_size_weight_type():

    def ask_td(jjj,ykw,ques):
        json_result=None
        return_td={'code':0,'err_msg':None,'ykw':ykw,'kw_s_w_type':None,'aff':0,'ind':jjj}
        for _ in range(3):
            res_cot=ask_deepseek_json(ques)
            if res_cot:
                try:
                    json_result=json.loads(res_cot)
                    kw_size=json_result['体积']
                    kw_weight=json_result['重量']
                    kw_s_w_type=f'体积{kw_size},重量{kw_weight}'
                    aff=tms.ExecNoQuery('update keyword_1688 set size_weight_type=? where keyword=?',(kw_s_w_type,ykw))
                    return_td['code']=0
                    return_td['err_msg']=None
                    return_td['aff']=aff
                    return_td['kw_s_w_type']=kw_s_w_type
                    break
                except Exception as e:
                    return_td['err_msg']=str(e)
                    return_td['code']=2
                    print(e)
            else:
                return_td['err_msg']='询问AI返回为空'
                return_td['code']=1
        
        return return_td

    sql_str1=f'''select top 5 keyword from keyword_1688 p
                    where p.keyword_type='广泛匹配关键词'
                    and size_weight_type is null
                    AND NOT EXISTS (
                        SELECT 1
                        FROM Blacklist b
                        WHERE b.word_language = '中文'
                        AND p.keyword LIKE '%' + b.word_blacklist + '%'
                    )
                    group by keyword
            '''
    rs1=tms.ExecQuery(sql_str1)
    print(f'本次共取出 {len(rs1)} 个关键词,来获取关键词类型')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            keyword=r[0]
            question1=f'''产品关键词:{keyword},
判断上面的产品关键词,体积大小(填大或者小),重量大小(填大或者小),只返回结果就行。
返回格式:{{"体积":"大","重量":"小"}}'''
            
            tasks.append(t.submit(ask_td,rj,keyword,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
                res_jjj=res['ind']
                res_kw=res['ykw']
                res_aff=res['aff']
                res_err=res['err_msg']
                res_kty=res['kw_s_w_type']
                err_msg=f',{res_err}' if res_err else ''
                print(f'第{res_jjj}个关键词:《{res_kw}》,提取重量体积:{res_kty}{err_msg},影响行数:{res_aff}')
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            
            print(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')

def task_extract_1688_product_tags():

    def ask_tag(jjj,pitem,ques):
        json_result=None
        pid,ptit=pitem
        return_td={'code':0,'err_msg':None,'pid':pid,'aff':0,'aff_relation':0,'ind':jjj,'tag_aff_dict':None,'aff_cgstate':0}
        for _ in range(3):
            res_cot=ask_deepseek_json(ques,frequency_penalty=0.1)
            if res_cot:
                try:
                    json_result=json.loads(res_cot)
                    aff=0
                    tag_aff_dict={}
                    for tag_type,tlist in json_result.items():
                        tag_aff_dict[tag_type]=0
                        for tag_name in tlist:
                            tdata={
                                'tag_name':tag_name,
                                'tag_type':tag_type,
                                'tag_source':'AI',
                                'source_product_id':pid
                            }
                            zds=list(tdata.keys())
                            paras=list(tdata.values())
                            aff+=tms.merge_into('tags',zds,paras,['source_product_id','tag_name'],True)
                            tag_aff_dict[tag_type]+=1
                    return_td['aff']=aff
                    aff_relation=0
                    rs_relation=tms.ExecQuery('select tag_id,tag_type,source_product_id from tags where source_product_id=?',(pid,))
                    for cur_tid,cur_tag_type,cur_pid in rs_relation:

                        redata={
                            'source_product_id':cur_pid,
                            'tag_id':cur_tid,
                            'tag_type':cur_tag_type,
                            'tag_source':'AI',
                            'relation_creator':'AI'
                        }
                        zds2=list(redata.keys())
                        paras2=list(redata.values())
                        aff_relation+=tms.merge_into('product_tag_relation',zds2,paras2,['source_product_id','tag_id'],True)

                    
                    return_td['code']=0
                    return_td['err_msg']=None
                    return_td['aff']=aff
                    return_td['aff_relation']=aff_relation
                    return_td['tag_aff_dict']=tag_aff_dict
                    if aff and aff_relation:
                        
                        aff_cgstate=tms.ExecNoQuery('update product_source_info set is_create_tag=1 where source_product_id=?',(pid,))
                        return_td['aff_cgstate']=aff_cgstate
                    break
                except Exception as e:
                    return_td['err_msg']=str(e)
                    return_td['code']=2
                    print(e)
                    continue
            else:
                return_td['err_msg']='询问AI返回为空'
                return_td['code']=1
        
        return return_td
    rs1=[]

    sql_str1=f'select top 1 source_product_id,title from product_source_info where is_create_tag=0'
    rs1.extend(tms.ExecQuery(sql_str1))
    print(f'本次共取出 {len(rs1)} 个标题,需要提取标签')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            title=r[1]
            question1=f'''{title}
从标题中提取标签，归类到（痛点解决类、产品属性类、适用场景类）
标签名称命名以解决痛点（止汗）出发，而非痛点（出汗）
每个分类可以有多个结果,所有字段值必须使用中文
返回格式:{{
"痛点解决类":["免打扰安装","遮阳防晒","便捷升降"],
"产品属性类":["卷帘","百叶窗帘","升降式"],
"适用场景类":["办公室","卫生间","厨房","窗户"]
}}'''
            
            tasks.append(t.submit(ask_tag,rj,r,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
                res_jjj=res['ind']
                res_pid=res['pid']
                res_aff=res['aff']
                res_aff2=res['aff_relation']
                res_aff_cgstate=res['aff_cgstate']
                res_tag_aff_dict=res['tag_aff_dict']
                res_err=res['err_msg']
                err_msg=f',{res_err}' if res_err else ''
                print(f'第{res_jjj}个货源产品ID:《{res_pid}》,tags表更新:{res_aff},product_tag_relation表更新:{res_aff2}\ntags类型分布:{res_tag_aff_dict},状态更改:{res_aff_cgstate}{err_msg}')
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            
            print(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')


def task_translate_1688_product_tags(lg):
    session=requests.session()
    session.headers=DS_Headers
    def ask_tag2(jjj,ctag_name,ques):
        return_td={'code':0,'err_msg':None,'tag_name':ctag_name,'aff':0,'aff_relation':0,'ind':jjj,'fy_res':None,'aff_cgstate':0}
        for _ in range(3):
            res_cot=ask_deepseek(session,ques)
            if res_cot:
                try:
                    aff=0
                    tdata={
                        'tag_name':ctag_name,
                        'language':lg,
                        'translated_text':res_cot
                    }
                    zds=list(tdata.keys())
                    paras=list(tdata.values())
                    aff=tms.merge_into('tags_translation',zds,paras,['tag_name','language'])
                    aff_relation=0
                    rs_relation=tms.ExecQuery('select id,translated_text from tags_translation where tag_name=? and language=?',(ctag_name,lg))
                    if len(rs_relation)>0:
                        tid=rs_relation[0][0]
                        translated_text=rs_relation[0][1]
                        kw_1688_data={
                            'keyword':translated_text,
                            'emarket':'shopee马来',
                            'gender':1,
                            'keyword_type':'选品关键词',
                            'language':lg
                        }
                        zds2=list(kw_1688_data.keys())
                        paras2=list(kw_1688_data.values())
                        aff_relation=tms.merge_into('keyword_1688',zds2,paras2,['keyword','language','emarket'],True)

                        
                        return_td['code']=0
                        return_td['aff']=aff
                        return_td['err_msg']=None
                        return_td['aff_relation']=aff_relation
                        return_td['fy_res']=res_cot
                        if aff and aff_relation:
                            aff_cgstate=tms.ExecNoQuery('update tags_translation set keyword_1688=1 where id=?',(tid,))
                            return_td['aff_cgstate']=aff_cgstate
                        break
                except Exception as e:
                    return_td['err_msg']=str(e)
                    return_td['code']=2
                    print(e)
                    continue
            else:
                return_td['err_msg']='询问AI返回为空'
                return_td['code']=1
        
        return return_td
    rs1=[]
    sql_str1=f'''select top 1 t.tag_name from tags t
                    where tag_type='痛点解决类' 
                    and not exists (
                        select 1 from tags_translation f where f.tag_name=t.tag_name and f.language='{lg}'
                    )
                    group by t.tag_type,t.tag_name 
                    having count(*)>=3
                '''
    rs1.extend(tms.ExecQuery(sql_str1))
    print(f'本次共取出 {len(rs1)} 个标签,需要翻译成 {lg}')
    with thddd.ThreadPoolExecutor(max_workers=10) as t:
        
        tasks=[]
        for rj,r in enumerate(rs1,start=1):
            tag_name=r[0]
            question1=f'''{tag_name}
将以上标签翻译成{lg}，只需要一个结果，电商平台最常用的，无需任何其他文字'''
            
            tasks.append(t.submit(ask_tag2,rj,tag_name,question1))


        for tjjj,t0 in enumerate(thddd.as_completed(tasks),start=1):
            try:
                res=t0.result()
                res_jjj=res['ind']
                res_tag_name=res['tag_name']
                res_aff=res['aff']
                res_aff2=res['aff_relation']
                res_aff_cgstate=res['aff_cgstate']
                res_fy=res['fy_res']
                res_err=res['err_msg']
                err_msg=f',{res_err}' if res_err else ''
                print(f'第{res_jjj}个标签:《{res_tag_name}》,{lg}翻译结果:{res_fy},《tags_translation》更新:{res_aff},《keyword_1688》更新:{res_aff2}\n,状态更改:{res_aff_cgstate}{err_msg}')
            except Exception as e:
                traceback.print_exc()
                print(f'线程错误 => {e}')

            
            print(f'本次剩余任务队列:{len(rs1)-tjjj}/{len(rs1)}')

task_translate_1688_product_tags('英语')